package com.windfone.dao.impl.jdbc.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.windfone.common.Constant;
import com.windfone.dao.MemberDao;
import com.windfone.model.Member;
import com.windfone.model.MemberDetail;
import com.windfone.model.Orderhistory;
import com.windfone.model.UserSendAddress;
public class MemberDaoImpl implements MemberDao {
	protected static Logger logger =Logger.getLogger(MemberDaoImpl.class);
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
		this.jdbcTemplate = jdbcTemplate;
	}
	String SQL_GET_ACOUNT_PRODUCT=" SELECT COUNT(1) FROM  member WHERE 1=1 ";
	public int getAllRecorderNumber( final String whereCondition ) {
		final List<Integer> result = new ArrayList<Integer>();
		String sql = SQL_GET_ACOUNT_PRODUCT + whereCondition;
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	
	String INSERT_MEMBER_SQL = " INSERT INTO member (name,loginName,cardId,level,points ,pointsb,status,password)  VALUES (?,?, ?,?,?,?,?,? ) ";
	public int addMember(final Member bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				INSERT_MEMBER_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getName());
						ps.setString(i++,bean.getLoginName());
						ps.setString(i++,bean.getCardId());
						ps.setInt(i++,bean.getLevel());
						ps.setInt(i++,bean.getPoints());
						ps.setInt(i++,bean.getPointsb());
						ps.setInt(i++,bean.getStatus());
						ps.setString(i++, com.windfone.common.StringUtil.encode(bean.getPassword()));
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;		
	}

	String UPDATE_MEMBER_SQL = " UPDATE member SET name=? ,loginName=?,cardId=? ,level=? ,points=?,pointsb=?,status=? ,password=? WHERE id=? ";
 
	public int updateMember(final Member bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				UPDATE_MEMBER_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getName());
						ps.setString(i++,bean.getLoginName());
						ps.setString(i++,bean.getCardId());
						ps.setInt(i++,bean.getLevel());
						ps.setInt(i++,bean.getPoints());
						ps.setInt(i++,bean.getPointsb());
						ps.setInt(i++,bean.getStatus());
						ps.setString(i++,com.windfone.common.StringUtil.encode(bean.getPassword()));
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	String UPDATE_MEMBER_NAME_SQL = " UPDATE member SET name=?  WHERE id=? ";
	
	public int updateMemberName(final Member bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_MEMBER_NAME_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setString(i++,bean.getName());
							ps.setInt(i++,bean.getId());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	String UPDATE_MEMBER_LEVEL_SQL = " UPDATE member SET level=?  WHERE id=? ";
	
	public int updateMemberLevl(final Member bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_MEMBER_LEVEL_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
						 
							ps.setInt(i++,bean.getLevel());
							ps.setInt(i++,bean.getId());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String DELETE_MEMBER_SQL = " DELETE FROM  member WHERE id=? ";
	public int deleteMember(final int key) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_MEMBER_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, key);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}


	String INSERT_MEMBER_DETAIL_SQL = " INSERT INTO memberdetail (memberId,sex,birthday,ageRange ,headerImage," +
			"email,mobileNumber,personId)  VALUES ( ?,?,?,?,? ,   ?,?,?) ";
	public int addMemberDetail(final MemberDetail bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_MEMBER_DETAIL_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getMemberId());
						ps.setInt(i++,bean.getSex());
						if (  bean.getBirthday() != null )
							ps.setDate(i++, new java.sql.Date(bean.getBirthday().getTime()));
						else 
							ps.setDate(i++,null);
						ps.setInt(i++,bean.getAgeRange());
						ps.setString(i++,bean.getHeaderImage());
						ps.setString(i++,bean.getEmail());
						ps.setString(i++,bean.getMobileNumber());
						ps.setString(i++,bean.getPersonId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;		
	}
	
//	headerImage=?,
	String UPDATE_MEMBER_DETAIL_SQL = " UPDATE memberdetail SET memberId=?,sex=?,birthday=?,ageRange=?,email=?,mobileNumber=?, personId=? WHERE id=?";
	public int updateMemberDetail(final MemberDetail bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_MEMBER_DETAIL_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getMemberId());
						ps.setInt(i++,bean.getSex());
						if (  bean.getBirthday() != null ){
//								System.out.println( bean.getBirthday() );
//								System.out.println( bean.getBirthday().getTime() );
							ps.setDate(i++, new java.sql.Date(bean.getBirthday().getTime()));
						}else 
							ps.setDate(i++,null);
						ps.setInt(i++,bean.getAgeRange());
//						ps.setString(i++,bean.getHeaderImage());
						ps.setString(i++,bean.getEmail());
						ps.setString(i++,bean.getMobileNumber());
						ps.setString(i++,bean.getPersonId());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String DELETE_MEMBER_DETAIL_SQL = " DELETE FROM  member WHERE id=? ";
	public int deleteMemberDetail(final int key) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_MEMBER_DETAIL_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, key);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	String GET_MEMBERs_SQL = "  SELECT * FROM  member   ";
	public List<Member> getMembers(int beginRecord, String whereCondition) {
		final List<Member> result = new ArrayList<Member>();
		String sql = GET_MEMBERs_SQL +" WHERE 1=1  "+ whereCondition + " ORDER BY id  LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Member to= new Member();
						to.setId(rs.getInt("id"));
						to.setLevel(rs.getInt("level"));
						to.setName(rs.getString("name"));
						to.setLoginName(rs.getString("loginName"));
						to.setPoints(rs.getInt("points"));
						to.setPointsb(rs.getInt("pointsb"));
						to.setStatus(rs.getInt("status"));
						to.setCardId(rs.getString("cardId"));
						to.setCreateTime(rs.getTimestamp("createTime"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	String DELETE_MEMBER_DETAIL_BYMEMBERID_SQL = " DELETE FROM  memberdetail WHERE memberId=? ";
	public int deleteMemberDetailByMemeberId(final int memberId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_MEMBER_DETAIL_BYMEMBERID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, memberId);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String GET_MEMBER_BYID_SQL = " SELECT * FROM  member  WHERE id=? ";
	public Member getMemberByKey(final int memberId) {
		final List<Member> result = new ArrayList<Member>();
		try {
			jdbcTemplate.query(
				GET_MEMBER_BYID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, memberId);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Member to= new Member();
 							to.setId(rs.getInt("id"));
 							to.setCardId(rs.getString("cardId"));
 							to.setLevel(rs.getInt("level"));
 							to.setName(rs.getString("name"));
 							to.setLoginName(rs.getString("loginName"));
 							to.setPoints(rs.getInt("points"));
 							to.setPointsb(rs.getInt("pointsb"));
 							to.setStatus(rs.getInt("status"));
 							to.setCreateTime(rs.getDate("createTime")); 							
 							to.setPassword( com.windfone.common.StringUtil.decode( rs.getString("password"))); 							
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else 
			return null;
	}
	
	String GET_MEMBERDETAIL_BYID_SQL = " SELECT * FROM  memberdetail  WHERE memberId=? ";
	public MemberDetail getMemberDetailByKey(final int memberId) {
		final List<MemberDetail> result = new ArrayList<MemberDetail>();
		try {
			jdbcTemplate.query(
					GET_MEMBERDETAIL_BYID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, memberId);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							MemberDetail to= new MemberDetail();
 							to.setId(rs.getInt("id"));
 							to.setAgeRange(rs.getInt("ageRange"));
 							to.setBirthday(rs.getDate("birthday"));
 							to.setLoginTime(rs.getTimestamp("loginTime"));
 							to.setEmail(rs.getString("email"));
 							to.setHeaderImage(rs.getString("headerImage"));
 							to.setMemberId(memberId);
 							to.setMobileNumber(rs.getString("mobileNumber"));
 							to.setPersonId(rs.getString("personId"));
 							to.setSex(rs.getInt("sex"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else 
			return null;
	}
	
	String GET_MEMBER_BY_USERNAME_SQL = " SELECT * FROM  member  WHERE loginName=? ";
	public boolean checkMember(final Member bean) {
		boolean rtv =false;
		final List<Member> result = new ArrayList<Member>();
		try {
			jdbcTemplate.query(
				GET_MEMBER_BY_USERNAME_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString(1, bean.getLoginName());
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Member to= new Member();
 							to.setId(rs.getInt("id"));
 							to.setCardId(rs.getString("cardId"));
 							to.setLevel(rs.getInt("level"));
 							to.setName(rs.getString("name"));
 							to.setLoginName(rs.getString("loginName"));
 							to.setPoints(rs.getInt("points"));
 							to.setPointsb(rs.getInt("pointsb"));
 							to.setStatus(rs.getInt("status"));
 							to.setCreateTime(rs.getDate("createTime")); 							
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0){
			rtv = true;
		}
		return rtv ;
	}
	String CHECK_MEMBER_BY_PASSWORD_SQL = " SELECT * FROM  member  WHERE id=? AND password=?";
	public boolean checkMemberBypwd(final Member bean) {
		boolean rtv =false;
		final List<Member> result = new ArrayList<Member>();
		try {
			jdbcTemplate.query(
					CHECK_MEMBER_BY_PASSWORD_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, bean.getId());
							ps.setString(2, com.windfone.common.StringUtil.encode(bean.getPassword()));
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Member to= new Member();
							to.setId(rs.getInt("id"));
							to.setCardId(rs.getString("cardId"));
							to.setLevel(rs.getInt("level"));
							to.setName(rs.getString("name"));
							to.setLoginName(rs.getString("loginName"));
							to.setPoints(rs.getInt("points"));
							to.setPointsb(rs.getInt("pointsb"));
							to.setStatus(rs.getInt("status"));
							to.setCreateTime(rs.getDate("createTime")); 							
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0){
			rtv = true;
		}
		return rtv ;
	}
	
	public Member getMemberByLoginName(final Member bean) {
		final List<Member> result = new ArrayList<Member>();
		try {
			jdbcTemplate.query(
					GET_MEMBER_BY_USERNAME_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setString(1, bean.getLoginName());
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Member to= new Member();
							to.setId(rs.getInt("id"));
							to.setCardId(rs.getString("cardId"));
							to.setLevel(rs.getInt("level"));
							to.setName(rs.getString("name"));
							to.setLoginName(rs.getString("loginName"));
							to.setPoints(rs.getInt("points"));
							to.setPointsb(rs.getInt("pointsb"));
							to.setStatus(rs.getInt("status"));
							to.setCreateTime(rs.getDate("createTime")); 							
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if ( result.size()>0){
				return result.get(0);
			} else 
				return null;
		}
	}
	
	String GET_MEMBER_BY_MOBILE_SQL = "SELECT * FROM  member  WHERE cardId=? ";
	public Member checkMemberByCardId(final Member bean) {
		final List<Member> result = new ArrayList<Member>();
		try {
			jdbcTemplate.query(
					GET_MEMBER_BY_USERNAME_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setString(1, bean.getCardId());
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Member to= new Member();
							to.setId(rs.getInt("id"));
							to.setCardId(rs.getString("cardId"));
							to.setLevel(rs.getInt("level"));
							to.setName(rs.getString("name"));
							to.setLoginName(rs.getString("loginName"));
							to.setPoints(rs.getInt("points"));
							to.setPointsb(rs.getInt("pointsb"));
							to.setStatus(rs.getInt("status"));
							to.setCreateTime(rs.getDate("createTime")); 							
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0){
			return result.get(0);
		} else 
			return null;
	}
	
	
	
	String INSERT_REGISTER_USER_SQL = " INSERT INTO member (loginName,level,points ,status,password)  VALUES (?,?,?,?,?) ";
	public int addRegisterUser(final Member bean) {
		int rtv = 0;
		logger.info("  addRegisterUser ");
		try {
			rtv = jdbcTemplate.update(
					INSERT_REGISTER_USER_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getLoginName());
						ps.setInt(i++,0);//level 0 注册会员
						ps.setInt(i++,0);//points
						ps.setInt(i++,1);//可用
						ps.setString(i++,bean.getPassword());//密码
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			logger.info("  addRegisterUser "+rtv);
		}
		return rtv;	
	}
	
	String INSERT_REGISTER_DETAIL_USER_SQL = " INSERT INTO memberdetail (memberId,email,sex)  VALUES (?,?,?) ";
	public int addRegisterDetailUser(final MemberDetail bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				INSERT_REGISTER_DETAIL_USER_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getMemberId());
						ps.setString(i++,bean.getEmail());
						ps.setInt(i++,bean.getSex());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return rtv;
		}
	}

	String GET_MEMBER_BY_CARDID_SQL=" SELECT * FROM  member  WHERE cardId=? AND password=? ";
	public Member loginByMobile(final String cardId, final String password) {
		final List<Member> result = new ArrayList<Member>();
		try {
			jdbcTemplate.query(
					GET_MEMBER_BY_CARDID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setString(1, cardId);
							ps.setString(2, password);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Member to= new Member();
							to.setId(rs.getInt("id"));
							to.setCardId(rs.getString("cardId"));
							to.setLevel(rs.getInt("level"));
							to.setName(rs.getString("name"));
							to.setLoginName(rs.getString("loginName"));
							to.setPoints(rs.getInt("points"));
							to.setPointsb(rs.getInt("pointsb"));
							to.setStatus(rs.getInt("status"));
							to.setCreateTime(rs.getDate("createTime")); 							
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0){
			return result.get(0);
		} else 
			return null;
	}

	String GET_MEMBER_BY_LOGINNAME_SQL=" SELECT * FROM  member  WHERE loginName=? AND password=? ";
	public Member loginByLoginName(final String loginName, final  String password) {
		final List<Member> result = new ArrayList<Member>();
		try {
			jdbcTemplate.query(
					GET_MEMBER_BY_LOGINNAME_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setString(1, loginName);
							ps.setString(2, password);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Member to= new Member();
							to.setId(rs.getInt("id"));
							to.setCardId(rs.getString("cardId"));
							to.setLevel(rs.getInt("level"));
							to.setName(rs.getString("name"));
							to.setLoginName(rs.getString("loginName"));
							to.setPoints(rs.getInt("points"));
							to.setPointsb(rs.getInt("pointsb"));
							to.setStatus(rs.getInt("status"));
							to.setCreateTime(rs.getDate("createTime")); 							
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if ( result.size()>0){
				return result.get(0);
			} else 
				return null;
		}
	}

	
	String INSERT_ORDER_HISTORY_SQL = " INSERT INTO orderhistory (producttype,productId,userId,price ,productName," +
	"buydate,remark,serialNumber,callback)  VALUES ( ?,?,?,?,? ,  ?,?,?,?) ";
 
	public int addOrderHistory(final Orderhistory bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_ORDER_HISTORY_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getProducttype());
						ps.setInt(i++,bean.getProductId());
						ps.setInt(i++,bean.getUserId());
						ps.setString(i++,bean.getPrice());
						ps.setString(i++,bean.getProductName());
						ps.setString(i++,bean.getBuydate());
						ps.setString(i++,bean.getRemark());
						ps.setString(i++,bean.getSerialNumber());
						ps.setString(i++,bean.getCallback());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;		
	}

 
	String UPDATE_ORDER_HISTORY_SQL = " UPDATE orderhistory SET producttype=?,productId=?,userId=?,price=?,productName=?,buydate=?, remark=? ,serialNumber=?,callback=? WHERE id=?";
	public int updateOrderHistory(final Orderhistory bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				UPDATE_ORDER_HISTORY_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getProducttype());
						ps.setInt(i++,bean.getProductId());
						ps.setInt(i++,bean.getUserId());
						ps.setString(i++,bean.getPrice());
						ps.setString(i++,bean.getProductName());
						ps.setString(i++,bean.getBuydate());
						ps.setString(i++,bean.getRemark());
						ps.setString(i++,bean.getSerialNumber());
						ps.setString(i++,bean.getCallback());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	
	String DELETE_ORDER_HISTORY_SQL = " DELETE FROM  orderhistory WHERE id=? ";
	
	public int deleOrderHistory(final Orderhistory bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_ORDER_HISTORY_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, bean.getId());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String GET_ORDERHISTORY_BY_USERID_SQL = " SELECT * FROM  orderhistory  WHERE userid=? ";
	public List<Orderhistory> listOrderHistoryByUserId(final int userId) {
		final List<Orderhistory> result = new ArrayList<Orderhistory>();
		try {
			jdbcTemplate.query(
					GET_ORDERHISTORY_BY_USERID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, userId);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Orderhistory to= new Orderhistory();
 							to.setId(rs.getInt("id"));
 							to.setProducttype(rs.getInt("producttype"));
 							to.setProductId(rs.getInt("productId"));
 							to.setUserId(rs.getInt("userId"));
 							to.setPrice(rs.getString("price"));
 							to.setProductName(rs.getString("productName"));
 							to.setBuydate(rs.getString("buydate"));
 							to.setRemark(rs.getString("remark"));
 							to.setSerialNumber(rs.getString("serialNumber"));
 							to.setCallback(rs.getString("callback"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result;
		else 
			return null;
	}
	String GET_ORDERHISTORY_BYID_SQL = " SELECT * FROM  orderhistory  WHERE id=? ";
	public Orderhistory getOrderHistoryById(final int purchaseHistoryId) {
		final List<Orderhistory> result = new ArrayList<Orderhistory>();
		try {
			jdbcTemplate.query(
					GET_ORDERHISTORY_BYID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, purchaseHistoryId);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Orderhistory to= new Orderhistory();
 							to.setId(rs.getInt("id"));
 							to.setProducttype(rs.getInt("producttype"));
 							to.setProductId(rs.getInt("productId"));
 							to.setUserId(rs.getInt("userId"));
 							to.setPrice(rs.getString("price"));
 							to.setProductName(rs.getString("productName"));
 							to.setBuydate(rs.getString("buydate"));
 							to.setRemark(rs.getString("remark"));
 							to.setSerialNumber(rs.getString("serialNumber"));
 							to.setCallback(rs.getString("callback"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else 
			return null;
	}

	public long addMemberGetReturnKey(final Member bean) {
		long rtv = 0;
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			jdbcTemplate.update(new PreparedStatementCreator(){   
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { 
					PreparedStatement ps =conn.prepareStatement(INSERT_MEMBER_SQL, new String[]{"id"});//返回id   
					int i=1;
					ps.setString(i++,bean.getName());
					ps.setString(i++,bean.getLoginName());
					ps.setString(i++,bean.getCardId());
					ps.setInt(i++,bean.getLevel());
					ps.setInt(i++,bean.getPoints());
					ps.setInt(i++,bean.getPointsb());
					ps.setInt(i++,bean.getStatus());
					ps.setString(i++,  com.windfone.common.StringUtil.encode(bean.getPassword()));  
	                return ps;   
				}
			},keyHolder);
			rtv = keyHolder.getKey().longValue();   
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;		
	}

	
	String UPDATE_LAST_ACCESS_SQL = " UPDATE memberdetail SET loginTime=? WHERE memberId=?";
	public int updateLastLogin(final Member memberBean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_LAST_ACCESS_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setTimestamp(i++, new java.sql.Timestamp( new java.util.Date().getTime()));
						ps.setInt(i++,memberBean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			return rtv;
		}
	}
 
	private int checkUserSEndAddressExist(final UserSendAddress bean){
		final List<Integer> result = new ArrayList<Integer>();
		String sql = "SELECT count(id) FROM userSendAddress WHERE memberid=? AND receiveName=?";
		try {
			jdbcTemplate.query(
					sql,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, bean.getMemberid());
							ps.setString(2, bean.getReceiveName());
						}
					},
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
							 
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	
	String INSERT_USER_SEND_ADDRESS_SQL =
		" INSERT INTO userSendAddress (memberid,receiveName,detailAddress,zipCode ,tel1," +
		"tel2,tel3,mobile,email)  VALUES ( ?,?,?,?,? ,  ?,?,?,?) ";
	public int addUserSendAddres(final UserSendAddress bean) {
		int rtv = 0;
		if ( checkUserSEndAddressExist( bean ) >0) {
			
		} else {
		try {
			rtv = jdbcTemplate.update(
				INSERT_USER_SEND_ADDRESS_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getMemberid());
						ps.setString(i++,bean.getReceiveName());
						ps.setString(i++,bean.getDetailAddress());
						ps.setString(i++,bean.getZipCode());
						ps.setString(i++,bean.getTel1());
						ps.setString(i++,bean.getTel2());
						ps.setString(i++,bean.getTel3());
						ps.setString(i++,bean.getMobile());
						ps.setString(i++,bean.getEmail());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		}
		return rtv;	
	}

	String UPDATE_USER_SEND_ADDRESS_SQL =
		" 	UPDATE  userSendAddress set receiveName=?,detailAddress=?,zipCode=? ,tel1=?," +
		"	tel2=?,tel3=?,mobile=?,email=? WHERE memberid=? ";
	public int updateUserSendAddres(final UserSendAddress bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_USER_SEND_ADDRESS_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getReceiveName());
						ps.setString(i++,bean.getDetailAddress());
						ps.setString(i++,bean.getZipCode());
						ps.setString(i++,bean.getTel1());
						ps.setString(i++,bean.getTel2());
						ps.setString(i++,bean.getTel3());
						ps.setString(i++,bean.getMobile());
						ps.setString(i++,bean.getEmail());
						ps.setInt(i++,bean.getMemberid());
			 
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	

	String DELETE_USER_SEND_ADDRESS_SQL = " DELETE FROM  userSendAddress WHERE id=? ";
	public int delUserSendAddres(final UserSendAddress bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_USER_SEND_ADDRESS_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, bean.getId());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String GET_USER_SEND_ADDRESS = " SELECT * FROM userSendAddress WHERE memberid=? ";
	public List<UserSendAddress> listUserSendAddres(final UserSendAddress bean) {
		final List<UserSendAddress> result = new ArrayList<UserSendAddress>();
		try {
			jdbcTemplate.query(
					GET_USER_SEND_ADDRESS,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getMemberid());
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							UserSendAddress to= new UserSendAddress();
							to.setDetailAddress(rs.getString("detailAddress"));
							to.setId(rs.getInt("id"));
							to.setMemberid(rs.getInt("memberid"));
							to.setMobile(rs.getString("mobile"));
							to.setReceiveName(rs.getString("receiveName"));
							to.setTel1(rs.getString("tel1"));
							to.setTel2(rs.getString("tel2"));
							to.setTel3(rs.getString("tel3"));
							to.setEmail(rs.getString("email"));
							to.setZipCode(rs.getString("zipCode"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	String COUNT_ONEUSERSEND_ADDRESS_SQL= " SELECT COUNT(id) FROM userSendAddress WHERE memberid=? ";
	public int countOneUserSendAddres(final UserSendAddress bean) {
		final List<Integer> result = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
					COUNT_ONEUSERSEND_ADDRESS_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getMemberid());
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0){
			 return result.get(0);
		}else
		return 0 ;
	}

	String GET_SEND_ADDRESS_SQL = " SELECT * FROM  userSendAddress WHERE id=?";
	public UserSendAddress getUserSendAddres(final UserSendAddress bean) {
		final List<UserSendAddress> result = new ArrayList<UserSendAddress>();
		try {
			jdbcTemplate.query(
					GET_SEND_ADDRESS_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getId());
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							UserSendAddress to= new UserSendAddress();
							to.setDetailAddress(rs.getString("detailAddress"));
							to.setId(rs.getInt("id"));
							to.setMemberid(rs.getInt("memberid"));
							to.setMobile(rs.getString("mobile"));
							to.setReceiveName(rs.getString("receiveName"));
							to.setTel1(rs.getString("tel1"));
							to.setTel2(rs.getString("tel2"));
							to.setTel3(rs.getString("tel3"));
							to.setZipCode(rs.getString("zipCode"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else
			return null;
	}

}
